<?php
/*
 *  @author Nicolas Genevaux
 * (c)nige 2008
 */
	/*  mysql date and time functions
	 * 
	 * 


CREATE TABLE `detailinscrit` (`identifiant` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `nom` VARCHAR(256) NOT NULL, `prenom` VARCHAR(256) NOT NULL, `sexe` VARCHAR(1) NOT NULL, `datenaissance` VARCHAR(4) NOT NULL, `mail` VARCHAR(256) NOT NULL) ENGINE = MyISAM
CREATE TABLE `exercice` (`identifiant` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`identifiantmatiere` INT NOT NULL,`identifiantinscrit` INT NOT NULL, `question` VARCHAR(4096) NOT NULL,`valide` VARCHAR(1) NOT NULL) ENGINE = MyISAM
CREATE TABLE `exercicechoix` (`identifiant` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`identifiantexercice` INT NOT NULL,`choix` VARCHAR(1024) NOT NULL, `reponse` VARCHAR(1) NOT NULL) ENGINE = MyISAM
CREATE TABLE `exercicerealise` (`identifiantexercice` INT NOT NULL,`identifiantinscrit` INT NOT NULL,`date` DATETIME NOT NULL,`reussi` VARCHAR(1) NOT NULL, `temps` INT NOT NULL) ENGINE = MyISAM
CREATE TABLE `inscrit` (`identifiant` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `login` VARCHAR(256) NOT NULL, `motdepasse` VARCHAR(256) NOT NULL, `adresseip` VARCHAR(256) NOT NULL, `derniereconnexion` DATETIME NOT NULL, `inscription` DATETIME NOT NULL, `points` FLOAT NOT NULL) ENGINE = MyISAM
CREATE TABLE `inscritmatiere` (`identifiant` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `identifiantinscrit` INT NOT NULL, `identifiantmatiere` INT NOT NULL, inscription DATETIME NOT NULL) ENGINE = MyISAM
CREATE TABLE `matiere` (`identifiant` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`ordre` INT NOT NULL, `matiere` VARCHAR(256) NOT NULL) ENGINE = MyISAM

CREATE VIEW inscritdetailinscrit AS SELECT inscrit.login,inscrit.motdepasse,detailinscrit.mail FROM inscrit,detailinscrit WHERE detailinscrit.mail!='' AND inscrit.identifiant=detailinscrit.identifiant
CREATE VIEW questionnaire AS SELECT exercice.identifiant,exercice.valide,exercice.identifiantinscrit,exercice.identifiantmatiere,matiere.matiere,exercice.question,exercicechoix1.identifiant AS idchoix1,exercicechoix1.choix AS choix1,exercicechoix2.identifiant AS idchoix2,exercicechoix2.choix AS choix2,exercicechoix3.identifiant AS idchoix3,exercicechoix3.choix AS choix3,exercicechoix4.identifiant AS idchoix4,exercicechoix4.choix AS choix4,IF(exercicechoix1.reponse='O',1,IF(exercicechoix2.reponse='O',2,IF(exercicechoix3.reponse='O',3,IF(exercicechoix4.reponse='O',4,0)))) AS reponse FROM exercice,matiere,exercicechoix exercicechoix1,exercicechoix exercicechoix2,exercicechoix exercicechoix3,exercicechoix exercicechoix4 WHERE exercicechoix1.identifiantexercice=exercice.identifiant AND exercicechoix2.identifiantexercice=exercice.identifiant AND exercicechoix3.identifiantexercice=exercice.identifiant AND exercicechoix4.identifiantexercice=exercice.identifiant AND exercicechoix1.identifiant!=exercicechoix2.identifiant AND exercicechoix1.identifiant!=exercicechoix3.identifiant AND exercicechoix1.identifiant!=exercicechoix4.identifiant AND exercicechoix2.identifiant!=exercicechoix3.identifiant AND exercicechoix2.identifiant!=exercicechoix4.identifiant AND exercicechoix3.identifiant!=exercicechoix4.identifiant AND exercice.identifiantmatiere=matiere.identifiant ORDER BY RAND()
CREATE VIEW exercicereussi AS SELECT identifiantexercice,COUNT(*) AS total FROM exercicerealise WHERE reussi='1' GROUP BY identifiantexercice
CREATE VIEW exercicerealisetotal AS SELECT identifiantexercice,COUNT(*) AS total FROM exercicerealise GROUP BY identifiantexercice
CREATE VIEW exercicevalide AS SELECT exercicerealisetotal.identifiantexercice,exercice.valide,IFNULL(ROUND(exercicereussi.total/exercicerealisetotal.total*100),0) AS ratio FROM exercice,exercicerealisetotal LEFT JOIN exercicereussi ON exercicerealisetotal.identifiantexercice=exercicereussi.identifiantexercice WHERE exercicerealisetotal.identifiantexercice=exercice.identifiant
CREATE VIEW exercicematiere AS SELECT identifiantmatiere,matiere,COUNT(*) AS total FROM exercice,matiere WHERE exercice.identifiantmatiere=matiere.identifiant  GROUP BY identifiantmatiere
CREATE VIEW inscritmatierenb AS SELECT identifiantmatiere,COUNT(*) AS total FROM inscritmatiere GROUP BY identifiantmatiere	
CREATE VIEW coefmatiere AS SELECT exercicematiere.identifiantmatiere,ROUND(inscritmatierenb.total/exercicematiere.total,2)  AS coefmatiere FROM exercicematiere,inscritmatierenb WHERE exercicematiere.identifiantmatiere=inscritmatierenb.identifiantmatiere
CREATE VIEW matiereinscritmatiere AS SELECT matiere.identifiant AS identifiantmatiere,matiere,identifiantinscrit FROM `matiere`,`inscritmatiere` WHERE matiere.identifiant=inscritmatiere.identifiantmatiere
CREATE VIEW exerciceinscritmatiere AS SELECT inscrit.identifiant,COUNT(*) AS total,exercice.identifiantmatiere,matiere.matiere,matiere.ordre FROM exercice,inscrit,matiere WHERE inscrit.identifiant=exercice.identifiantinscrit AND exercice.identifiantmatiere=matiere.identifiant AND exercice.valide='N' GROUP BY matiere.matiere 
CREATE VIEW exercicematieredateok AS SELECT exercicerealise.identifiantinscrit,exercice.identifiantmatiere,DATE(exercicerealise.date) AS date,AVG(exercicerealise.temps) AS temps,COUNT(*) AS total FROM `exercicerealise`,exercice WHERE exercicerealise.reussi='1' AND exercicerealise.identifiantexercice=exercice.identifiant GROUP BY DATE(exercicerealise.date),exercicerealise.identifiantinscrit,exercice.identifiantmatiere ORDER BY exercicerealise.identifiantinscrit,exercice.identifiantmatiere,DATE(exercicerealise.date)
CREATE VIEW exercicematieredateko AS SELECT exercicerealise.identifiantinscrit,exercice.identifiantmatiere,DATE(exercicerealise.date) AS date,AVG(exercicerealise.temps) AS temps,COUNT(*) AS total FROM `exercicerealise`,exercice WHERE exercicerealise.reussi='0' AND exercicerealise.identifiantexercice=exercice.identifiant GROUP BY DATE(exercicerealise.date),exercicerealise.identifiantinscrit,exercice.identifiantmatiere ORDER BY exercicerealise.identifiantinscrit,exercice.identifiantmatiere,DATE(exercicerealise.date)
CREATE VIEW exercicematieredate AS SELECT exercicematieredateok.identifiantinscrit AS identifiantinscrit,exercicematieredateok.identifiantmatiere AS identifiantmatiere,exercicematieredateok.date AS date,100*exercicematieredateok.total/(IFNULL(exercicematieredateko.total,0)+exercicematieredateok.total) AS reussite, IFNULL(exercicematieredateko.temps,0)+exercicematieredateok.temps AS temps,100*exercicematieredateok.total/(IFNULL(exercicematieredateko.total,0)+exercicematieredateok.total) /(IFNULL(exercicematieredateko.temps,0)+exercicematieredateok.temps) AS taux FROM exercicematieredateko RIGHT JOIN exercicematieredateok ON exercicematieredateok.identifiantinscrit=exercicematieredateko.identifiantinscrit AND exercicematieredateok.identifiantmatiere=exercicematieredateko.identifiantmatiere AND exercicematieredateok.date=exercicematieredateko.date
CREATE VIEW exercicerealiseageko AS SELECT exercicerealise.identifiantexercice,COUNT(*) AS total, YEAR(exercicerealise.date)-detailinscrit.datenaissance AS age FROM exercicerealise,detailinscrit WHERE detailinscrit.identifiant=exercicerealise.identifiantinscrit AND exercicerealise.reussi='0' GROUP BY exercicerealise.identifiantinscrit,exercicerealise.identifiantexercice ORDER BY identifiantexercice
CREATE VIEW exercicerealiseageok AS SELECT exercicerealise.identifiantexercice,COUNT(*) AS total, YEAR(exercicerealise.date)-detailinscrit.datenaissance AS age FROM exercicerealise,detailinscrit WHERE detailinscrit.identifiant=exercicerealise.identifiantinscrit AND exercicerealise.reussi='1' GROUP BY exercicerealise.identifiantinscrit,exercicerealise.identifiantexercice ORDER BY identifiantexercice
CREATE VIEW exercicerealiseage AS SELECT exercicerealiseageko.identifiantexercice,exercicerealiseageko.age, IFNULL(exercicerealiseageko.total/(exercicerealiseageko.total+exercicerealiseageok.total) *100,0) AS ratio FROM exercicerealiseageko LEFT JOIN exercicerealiseageok ON exercicerealiseageok.identifiantexercice=exercicerealiseageko.identifiantexercice AND exercicerealiseageok.age=exercicerealiseageko.age


	*	*	*/
?>
